package com.sq.partner.manager.controller;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.sq.partner.manager.common.Config;
import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.common.Result;
import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.mybatis.model.CoreDept;
import com.sq.partner.manager.mybatis.model.GatewayChannelInfo;
import com.sq.partner.manager.mybatis.model.GatewayManual;
import com.sq.partner.manager.mybatis.model.GatewayTrans;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.NocarPlan;
import com.sq.partner.manager.service.IGatewayChannelInfoConfigService;
import com.sq.partner.manager.service.IGatewayDataManageService;
import com.sq.partner.manager.util.DBMap;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.StrUtil;
import com.sq.partner.manager.util.WebServiceUtil;
/**
 * 
 * 数据维护
 */
@Controller
@RequestMapping(value = "/dataManage")
public class DataManageController {
	
	@Resource
	private IGatewayDataManageService dataManageService;
	@Resource
	private IGatewayChannelInfoConfigService channelInfoConfigService;
	
	private static Logger logger = LogUtil.getLog(DataManageController.class);
	
	/**
	 * 进入dataManage.jsp时跳转
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/dataManage")
    public String gatewayTrans() throws Exception {
        return "dataManage/dataManage";
    }
	
	/**
	 * 查询有权限的合作伙伴对应的List<app>
	 * @param session
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/queryPrivilegeBranch")
	@ResponseBody
    public List<CoreDept> queryAppList(HttpSession session) throws Exception {
		GatewayUser user=(GatewayUser) session.getAttribute(Constants.SESSION_USER);
		if(null!=user && StrUtil.isNotEmpty(user.getDepartmentStr())){
			return dataManageService.queryDeptList(user);
		}
		return null;
    }
	
	
	/**
	 * 根据Appid查询与之对应的ChannelInfo
	 * @param fkAppid
	 * @return List<GatewayChannelInfo>
	 * @throws Exception 
	 */
	@RequestMapping("/loadChannelInfoByAppid")
	public @ResponseBody Page<GatewayChannelInfo> loadChannelInfoByAppid(Integer size,Integer pageIndex,String deptCode,String channelName,String channelCode) throws Exception{
		Page<GatewayChannelInfo> page = new Page<GatewayChannelInfo>();
		if(StrUtil.isEmpty(deptCode)){
			page.setResult("error");
			page.setMessage("查询失败，机构字段不能为空！");
			return page;
		}
		page.setCurrent(pageIndex);
		page.setSize(size);
		GatewayChannelInfo channelInfo = new GatewayChannelInfo();
		channelInfo.setDeptCode(deptCode);
		channelInfo.setChannelCode(channelCode);
		channelInfo.setChannelName(channelName);
		page.setType(channelInfo);
		return channelInfoConfigService.getChannelInfoListByDeptCode(page);
	}
	
	@RequestMapping(value = "/loadCoreDept")
	public @ResponseBody Page<CoreDept> queryAllCoreDepts(Integer size,Integer pageIndex,String deptCode,String deptCNname,String parentCode) throws Exception{
		Page<CoreDept> page=new Page<CoreDept>();
		if(StrUtil.isEmpty(parentCode)){
			page.setResult("error");
			page.setMessage("查询失败，机构字段不能为空！");
			return page;
		}
		page.setCurrent(pageIndex);
		page.setSize(size);
		CoreDept dept=new CoreDept();
		dept.setDeptCode(deptCode);
		dept.setDeptCname(deptCNname);
		page.setType(dept);
		Page<CoreDept> page2=dataManageService.queryPageForCoreDept(page,parentCode);
		return page2;
	}
	
	@RequestMapping(value = "/loadInsurePlan")
	public @ResponseBody Page<NocarPlan> loadInsurePlanByChannelCode(Integer size,Integer pageIndex,String planCode,String planName,String channelCode) throws Exception{
		Page<NocarPlan> page=new Page<NocarPlan>();
		if(StrUtil.isEmpty(channelCode)){
			page.setResult("error");
			page.setMessage("查询失败，合作伙伴字段不能为空！");
			return page;
		}
		page.setCurrent(pageIndex);
		page.setSize(size);
		NocarPlan plan=new NocarPlan();
		plan.setPlanCode(planCode);
		plan.setPlanName(planName);
		page.setType(plan);
		Page<NocarPlan> page2=dataManageService.queryPageForInsurePlanList(page,channelCode);
		return page2;
	}
	
	/**
	 * 条件查询trans
	 * @param session
	 * @param appId
	 * @param channelCode
	 * @param checkFlag
	 * @param polno
	 * @param trxdateBegin
	 * @param trxdateEnd
	 * @param pageIndex
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/queryTransByCriteria")
	@ResponseBody
    public Map queryTransByCriteria(HttpSession session,String brachCode,String channelCode,String planCode,String subDeptCode, Integer checkFlag,String polno,String confirmno,String trxdateBegin,String trxdateEnd, Integer pageIndex,String subPartnerNo) 
    	   throws Exception {
		Map<String,Page> tem = new HashMap<String,Page>();
		Page p = new Page();
		if(!(channelInfoConfigService.checkIsHasPrivilege(session, brachCode))){
			p.setMessage("权限不足，无权限使用对账管理查询!");
			p.setResult("error");
			tem.put("page", p);
			return tem;
		}else if(StrUtil.isEmpty(brachCode)){
			p.setMessage("机构字段不能为空!");
			p.setResult("error");
			tem.put("page", p);
			return tem;
		}
		GatewayUser user = (GatewayUser)session.getAttribute(Constants.SESSION_USER);
		//总行和分行
		boolean isHQFlag = false;
		if(null != user && StrUtil.isNotEmpty(user.getDepartmentStr())){
			isHQFlag = user.getDepartmentStr().contains(Constants.HQ_BRANCH_CODE);
		}
		user.setHQFlag(isHQFlag);
		
		GatewayTrans trans = new GatewayTrans();
		trans.setBranchCode(brachCode);
		trans.setChannelCode(channelCode);
		trans.setChannelProductcode(planCode);
		trans.setMakechannel(subDeptCode);
		trans.setCheckFlag(checkFlag);
		trans.setPolno(polno);
		trans.setConfirmno(confirmno);
		trans.setTrxdateBegin(trxdateBegin);
		trans.setTrxdateEnd(trxdateEnd);
		trans.setSubPartnerNo(subPartnerNo);
		return dataManageService.queryTransMapByCriteria(trans, pageIndex, user);
    }
	
	/**
	 * 模板下载
	 * @param request
	 * @param response
	 * @throws IOException 
	 * @throws Exception
	 */
	@RequestMapping("/downloadReconciliationMould")
	public void downloadReconciliationMould(HttpServletResponse response,HttpSession session) throws IOException{
		
		String path = Thread.currentThread().getContextClassLoader().getResource("").getPath();
		String projectRoot = path.substring(0, path.indexOf("/WEB-INF"));
		
		StringBuilder root = new StringBuilder(projectRoot);
		
		  if (!root.toString().endsWith(java.io.File.separator)) {
		        root = root.append(java.io.File.separator);
		  }
		  
		  root = root.append("reconciliationTemplateFile.xls");
		 
		 String dir = root.toString();
		 logger.info("对账导入文件模板位置："+dir);
		 
		 File myFile=null;
		 ServletOutputStream outPutStream=null;
		 InputStream inputStream=null;
		 try{
			 myFile=new File(dir); 
			 if(myFile.exists()){
				 String fileName=java.net.URLEncoder.encode("reconciliationTemplateFile.xls","UTF-8");  
				 response.setHeader("Content-disposition","attachment; filename="+ fileName);  
				 response.setContentType("application/x-download"); 
		         outPutStream=response.getOutputStream();  
		         inputStream=new  BufferedInputStream(new FileInputStream(myFile));  
		         byte[] bytes=new byte[10240];
		         int len = 0;
		         while((len = inputStream.read(bytes)) != -1)  
		         {    
		             outPutStream.write(bytes,0,len);   
		         } 
		         outPutStream.flush();
			  }
		    }catch(IOException e){
		    	logger.info("对账导入模板文件不存在",e);
		  }finally{
			  if(inputStream!=null){
				  inputStream.close();
			  }
			  if(outPutStream!=null){
				  outPutStream.close();
			  }
		  }
	}
	
	/**
	 * 手动对账
	 */
	@RequestMapping("/manualReconciliation")
	public String manualReconciliation(HttpSession session,@RequestParam(value = "reconciliationFile") MultipartFile reconciliationFile,Model model)
		   throws  Exception{
		InputStream input = null;
		try{
			input = reconciliationFile.getInputStream();
			GatewayUser user = (GatewayUser)session.getAttribute(Constants.SESSION_USER);
			
		    List<GatewayManual> reconciliationFileDataList = dataManageService.readReconciliationXls(input);
		    Map<String,Integer> result = dataManageService.reconciliation(reconciliationFileDataList,user);

		    model.addAllAttributes(result);
		}catch(AppException a){
			model.addAttribute("appException",a.getMessage());
			logger.info(a);
		}finally{
			try {
				if(null!=input){
					input.close();
				}
			} catch (Exception e) {
				logger.warn("关闭流失败！", e);
			}
		}
		return "dataManage/dataManage";
	}
	
	/**
	 * 交易记录文件下载
	 */
	@RequestMapping("/downloadTransFile")
	@ResponseBody
	public String downloadTransFile(HttpServletRequest request, HttpServletResponse response,HttpSession session,
			String privilegeBranch,String instCode,String prodInstCode,String dptCode, Integer checkSelect,String polno,String confirmno,String trxdateBegin,String trxdateEnd) throws Exception{
		
		GatewayUser user = (GatewayUser)session.getAttribute(Constants.SESSION_USER);
		
		GatewayTrans trans = new GatewayTrans();
		trans.setBranchCode(privilegeBranch);
		trans.setChannelCode(instCode);
		trans.setChannelProductcode(prodInstCode);
		trans.setMakechannel(dptCode);
		trans.setCheckFlag(checkSelect);
		trans.setPolno(polno);
		trans.setConfirmno(confirmno);
		trans.setTrxdateBegin(trxdateBegin);
		trans.setTrxdateEnd(trxdateEnd);
		
		Page transPage = dataManageService.queryTransByCriteria(trans, null, user);
		
		List list = transPage.getData();
		if(list != null && list.size() > 0){
			
			// 第一步，创建一个webbook，对应一个Excel文件
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
 			HSSFSheet sheet = workbook.createSheet("交易数据表");
 			
 			sheet.setColumnWidth(0, 30*256);
 			sheet.setColumnWidth(1, 16*256);
 			sheet.setColumnWidth(2, 12*256);
 			sheet.setColumnWidth(3, 30*256);
 			sheet.setColumnWidth(4, 30*256);
 			sheet.setColumnWidth(5, 30*256);
 			sheet.setColumnWidth(6, 30*256);
 			sheet.setColumnWidth(7, 16*256);
 			sheet.setColumnWidth(8, 10*256);
 			
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
 			HSSFRow row = sheet.createRow(0);
			// 第四步，创建单元格，并设置值表头 设置表头居中
 			HSSFCellStyle style = workbook.createCellStyle();
 			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	 
 			HSSFCell cell = row.createCell(0);
 			cell.setCellValue("合作伙伴");
 			cell.setCellStyle(style);
 			HSSFCell cell1 = row.createCell(1);
 			cell1.setCellValue("交易日期");
 			cell1.setCellStyle(style);
 			HSSFCell cell2 = row.createCell(2);
 			cell2.setCellValue("交易名称");
 			cell2.setCellStyle(style);
 			HSSFCell cell3 = row.createCell(3);
 			cell3.setCellValue("保单号");
 			cell3.setCellStyle(style);
 			HSSFCell cell4 = row.createCell(4);
 			cell4.setCellValue("批单号");
 			cell4.setCellStyle(style);
 			HSSFCell cell5 = row.createCell(5);
 			cell5.setCellValue("含税保费");
 			cell5.setCellStyle(style);
 			HSSFCell cell6 = row.createCell(6);
 			cell6.setCellValue("手续费");
 			cell6.setCellStyle(style);
 			HSSFCell cell7 = row.createCell(7);
 			cell7.setCellValue("生效日期");
 			cell7.setCellStyle(style);
 			HSSFCell cell8 = row.createCell(8);
 			cell8.setCellValue("对账状态");
 			cell8.setCellStyle(style);
			
	        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
	        for (int i = 0; i < list.size(); i++) {
				row = sheet.createRow(i+1);
				DBMap map = (DBMap)list.get(i);
				
				String channelName;
				if(null != map.get("CHANNEL_NAME")){
					channelName = map.get("CHANNEL_NAME").toString();
				}else{
					channelName = "";
				}
				
				String trxdate;
				if(null != map.get("TRXDATESTR")){
					trxdate = map.get("TRXDATESTR").toString();
				}else{
					trxdate = "";
				}
				
				String transName;
				if(null != map.get("TRANS_NAME")){
					transName = map.get("TRANS_NAME").toString();
				}else{
					transName = "";
				}
				
				String polno1;
				if(null != map.get("POLNO")){
					polno1 = map.get("POLNO").toString();
				}else{
					polno1 = "";
				}
				
				String wconfirmno;
				if(null != map.get("CONFIRMNO")){
					wconfirmno = map.get("CONFIRMNO").toString();
				}else{
					wconfirmno = "";
				}
				
				String premiun;
				if(null != map.get("PREMIUM")){
					premiun = map.get("PREMIUM").toString();
				}else{
					premiun = "";
				}
				
				String agentfee;
				if(null != map.get("AGENTFEE")){
					agentfee = map.get("AGENTFEE").toString();
				}else{
					agentfee = "";
				}
				
				String startTime;
				if(null != map.get("STARTTIMESTR")){
					startTime = map.get("STARTTIMESTR").toString();
				}else{
					startTime = "";
				}
				
				String checkFlag1;
				String checkFlagName;
				if(null != map.get("CHECK_FLAG")){
					checkFlag1 = map.get("CHECK_FLAG").toString();
					if(checkFlag1.equals("-1")){
						checkFlagName = "无需对账";
					}else if(checkFlag1.equals("0")){
						checkFlagName = "未对账";
					}else if(checkFlag1.equals("1")){
						checkFlagName = "已对账";
					}else if(checkFlag1.equals("2")){
						checkFlagName = "对账失败";
					}else if(checkFlag1.equals("3")){
						checkFlagName = "未知数据";
					}else{
						checkFlagName = "";
					}
				}else{
					checkFlagName = "";
				}
				
				row.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue(channelName);
				row.createCell(1, HSSFCell.CELL_TYPE_STRING).setCellValue(trxdate);
				row.createCell(2, HSSFCell.CELL_TYPE_STRING).setCellValue(transName);
				row.createCell(3, HSSFCell.CELL_TYPE_STRING).setCellValue(polno1);
				row.createCell(4, HSSFCell.CELL_TYPE_STRING).setCellValue(wconfirmno);
				row.createCell(5, HSSFCell.CELL_TYPE_STRING).setCellValue(premiun);
				row.createCell(6, HSSFCell.CELL_TYPE_STRING).setCellValue(agentfee);
				row.createCell(7, HSSFCell.CELL_TYPE_STRING).setCellValue(startTime);
				row.createCell(8, HSSFCell.CELL_TYPE_STRING).setCellValue(checkFlagName);
	        }
	
	        String fileName=java.net.URLEncoder.encode("Export-File.xls","UTF-8");  
	        response.setHeader("Content-disposition","attachment; filename="+fileName);  
			response.setContentType("application/x-download"); 
	        OutputStream out=response.getOutputStream();  
	        
	        workbook.write(out);
	        out.flush();
	        out.close();
		}
		
		return "dataManage/dataManage";
	}
	/**已对账*/
	@RequestMapping(value="reconciled")
	@ResponseBody
	public Result reconciled(GatewayTrans  gatewayTrans){
		if(gatewayTrans==null||StrUtil.isEmpty(gatewayTrans.getTransid())){
			return Result.error("参数错误");
		}
		gatewayTrans.setCheckFlag(1);
		try {
			return dataManageService.reconciled(gatewayTrans);
		} catch (Exception e) {
			
			logger.info("对账失败",e);
			return Result.error("操作失败");
		}
		
	}
	/**保单注销*/
	@RequestMapping(value="policyCancel")
	@ResponseBody
	public Result policyCancel(GatewayTrans  gatewayTrans){
		if(gatewayTrans==null||StrUtil.isEmpty(gatewayTrans.getTransid())){
			return Result.error("参数错误");
		}
		try {
			return dataManageService.policyCancel(gatewayTrans);
		} catch (Exception e) {
			
			logger.info("对账失败",e);
			return Result.error("操作失败");
		}
		
	}
	
}